﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Xml;
using System.Configuration;
using ZipApplication;

namespace ToolsDb
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        public string str = "Data Source=[dburl];Initial Catalog=[dbname];User ID=[dbuser];password=[dbpwd]";
        public string namespacestr = "";
        public string e_namespacestr = "";
        public string entitysname = "";
        public string path = "";
        public string btntype = "";
        string[] valuetype = { "int", "long", "decimal", "float", "double", "DateTime" };
        /// <summary>
        /// 连接数据库并拿到相应的数据
        /// 并且绑定到combox控件上
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            str = str.Replace("[dburl]", this.txtDbUrl.Text.Trim()).Replace("[dbname]", this.txtDbName.Text.Trim()).Replace("[dbuser]", this.txtDbUser.Text.Trim()).Replace("[dbpwd]", this.txtDbPwd.Text.Trim());
            SqlConnection conn = new SqlConnection(str);
            try
            {
                conn.Open();
                SqlDataAdapter sdr = new SqlDataAdapter("Exec sp_helpdb", conn);
                DataSet ds = new DataSet();
                sdr.Fill(ds);
                this.comboBox1.DataSource = ds.Tables[0].DefaultView;
                this.comboBox1.DisplayMember = "name";
                this.comboBox1.SelectedIndex = 0;
                this.button1.Enabled = false;
                this.label1.Text = "连接成功并拿到数据！~！";
                this.panel1.Enabled = true;
                SaveAppConfig("dburl", this.txtDbUrl.Text.Trim());
                SaveAppConfig("dbname", this.txtDbName.Text.Trim());
                SaveAppConfig("dbuser", this.txtDbUser.Text.Trim());
                SaveAppConfig("dbpwd", this.txtDbPwd.Text.Trim());
            }
            catch (Exception ex)
            {
                str = "Data Source=[dburl];Initial Catalog=[dbname];User ID=[dbuser];password=[dbpwd]";
                this.button1.Enabled = true;
                this.label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>        
        /// 断开连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            str = "Data Source=[dburl];Initial Catalog=[dbname];User ID=[dbuser];password=[dbpwd]";
            this.button1.Enabled = true;
            this.label1.Text = "连接已断开！~！";
            this.panel1.Enabled = false;
        }
        /// <summary>
        /// 窗体加载事件让面板的可见属性为false
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            this.txtDbUrl.Text = ConfigurationSettings.AppSettings["dburl"].ToString();
            this.txtDbName.Text = ConfigurationSettings.AppSettings["dbname"].ToString();
            this.txtDbUser.Text = ConfigurationSettings.AppSettings["dbuser"].ToString();
            this.txtDbPwd.Text = ConfigurationSettings.AppSettings["dbpwd"].ToString();
            this.txtNameSpace.Text = ConfigurationSettings.AppSettings["namespaces"].ToString();
            this.txtPath.Text = ConfigurationSettings.AppSettings["path"].ToString();
            path = ConfigurationSettings.AppSettings["path"].ToString() + @"\"; ;
            this.label1.Text = "";
            this.panel1.Enabled = false;
        }
        /// <summary>
        /// 下拉列表的索引改变事件
        /// 首先让checkedListBox的列清空
        /// 在填充新值
        /// </summary>并绑定
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comboBox1_selectedIndexChanged(object sender, EventArgs e)
        {
            this.checkedListBox1.Items.Clear();
            SqlConnection conn = new SqlConnection(str);
            try
            {
                conn.Open();
                string dbname = string.Empty;
                //if (this.comboBox1.Text.Contains("."))
                //{
                dbname = "[" + this.comboBox1.Text + "]";
                //}
                //string sql = "use " + this.comboBox1.Text + "  ; select name from dbo.sysobjects  where xtype='U' and sysstat<200 order by name";
                string sql = "use " + dbname + ";" + @"SELECT
A.name,
C.value
FROM sys.tables A
left JOIN sys.extended_properties C ON C.major_id = A.object_id  and minor_id=0
order by name";
                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    this.label1.Text = "数据库里没有表！";
                }
                while (reader.Read())
                {
                    //for (int i = 0; i < reader.FieldCount; i++)
                    //{
                    //    this.checkedListBox1.Items.Add(reader["name"] + "/" + reader["value"]);
                    //}
                    this.checkedListBox1.Items.Add(reader["name"] + "/" + reader["value"]);
                    this.label1.Text = "已成功的得到该数据库下的所有表！！";
                } reader.Close();
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 这个方法是得到这张表是否有外间存在
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public Dictionary<string, string> GetMethod(string table)
        {
            Dictionary<string, string> list = new Dictionary<string, string>();
            SqlConnection conn = new SqlConnection(str);
            try
            {
                conn.Open();
                string dbname = string.Empty;
                //if (this.comboBox1.Text.Contains("."))
                //{
                dbname = "[" + this.comboBox1.Text + "]";
                //}
                SqlCommand commend = new SqlCommand("use " + dbname + "  ;EXEC sp_fkeys @fktable_name = N'" + table + "'", conn);
                SqlDataReader reader = commend.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader["FKCOLUMN_NAME"].ToString(), reader["PKTABLE_NAME"].ToString());

                } reader.Close();
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }

        //这个方法是查询出主表的那个字段所对应的
        public Dictionary<string, string> Getfield(string table)
        {
            Dictionary<string, string> list = new Dictionary<string, string>();
            SqlConnection conn = new SqlConnection(str);
            try
            {
                conn.Open();
                string dbname = string.Empty;
                //if (this.comboBox1.Text.Contains("."))
                //{
                dbname = "[" + this.comboBox1.Text + "]";
                //}
                SqlCommand commend = new SqlCommand("use " + dbname + "  ;EXEC sp_fkeys @fktable_name = N'" + table + "'", conn);
                SqlDataReader reader = commend.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader["FKCOLUMN_NAME"].ToString(), reader["PKTABLE_NAME"].ToString() + "." + reader["PKCOLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["PKCOLUMN_NAME"].ToString().Substring(1));

                } reader.Close();
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        /// <summary>
        /// 这个方法是获取表中所有的字段和字段类型，是否为空
        /// </summary>
        /// <param name="list"></param>
        /// <param name="classname"></param>
        public Dictionary<string, Tuple<string, string>> Print(string classname)
        {
            //Dictionary<string, string> list = GetMethod(classname);
            Dictionary<string, string> list = new Dictionary<string, string>();
            Dictionary<string, Tuple<string, string>> wanz = new Dictionary<string, Tuple<string, string>>();
            SqlConnection conn = new SqlConnection(str);
            string dbname = string.Empty;
            //if (this.comboBox1.Text.Contains("."))
            //{
            dbname = "[" + this.comboBox1.Text + "]";
            //}
            string sql = "use " + dbname + "  ; EXEC sp_columns '" + classname + "'";
            try
            {
                conn.Open();
                SqlCommand command1 = new SqlCommand(sql, conn);
                SqlDataReader reader = command1.ExecuteReader();
                while (reader.Read())
                {
                    //if (list != null && list.Count > 0)
                    //{
                    //    foreach (string id in list.Keys)
                    //    {
                    //        if (reader["COLUMN_NAME"].Equals(id))
                    //        {
                    //            wanz.Add(list[id].ToString(), list[id]);//list[id].ToString().Substring(0, 1).ToLower() + list[id].Substring(1).ToString()
                    //            break;
                    //        }
                    //        else
                    //        {
                    //            if (reader["TYPE_NAME"].ToString() == "money" || reader["TYPE_NAME"].ToString() == "decimal")
                    //            {
                    //                wanz.Add(reader["COLUMN_NAME"].ToString(), "decimal");//reader["COLUMN_NAME"].ToString().Substring(0, 1).ToLower() + reader["COLUMN_NAME"].ToString().Substring(1)
                    //            }
                    //            else
                    //            {
                    //                wanz.Add(reader["COLUMN_NAME"].ToString(), getType(reader["TYPE_NAME"].ToString()));//reader["COLUMN_NAME"].ToString().Substring(0, 1).ToLower() + reader["COLUMN_NAME"].ToString().Substring(1)
                    //            }                                
                    //            break;
                    //        }
                    //    }
                    //}
                    //else
                    //{
                    if (reader["TYPE_NAME"].ToString() == "money" || reader["TYPE_NAME"].ToString() == "decimal")
                    {
                        wanz.Add(reader["COLUMN_NAME"].ToString(), new Tuple<string, string>("decimal", reader["IS_NULLABLE"].ToString()));//reader["COLUMN_NAME"].ToString().Substring(0, 1).ToLower() + reader["COLUMN_NAME"].ToString().Substring(1)
                    }
                    else
                    {
                        wanz.Add(reader["COLUMN_NAME"].ToString(), new Tuple<string, string>(getType(reader["TYPE_NAME"].ToString()), reader["IS_NULLABLE"].ToString()));//reader["COLUMN_NAME"].ToString().Substring(0, 1).ToLower() + reader["COLUMN_NAME"].ToString().Substring(1)
                    }                    
                    //}
                } reader.Close();
                //this.label1.Text = "已成功的得到该数据库下的所有表！！";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return wanz;

        }
        /// <summary>
        /// 这个方法是获取表中所有的字段的注释
        /// </summary>
        /// <param name="classname"></param>
        /// <returns></returns>
        public Dictionary<string, string> PrintExplain(string classname)
        {
            Dictionary<string, string> list = new Dictionary<string, string>();
            SqlConnection conn = new SqlConnection(str);
            string dbname = string.Empty;
            //if (this.comboBox1.Text.Contains("."))
            //{
            dbname = "[" + this.comboBox1.Text + "]";
            //}
            string sql = "use " + dbname + @"  ; SELECT t.[name] AS tablename,c.[name] AS columnname,cast(ep.[value] 
as varchar(100)) AS explain
FROM sys.tables AS t
INNER JOIN sys.columns 
AS c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties AS ep 
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1 
AND t.name='" + classname + "'";
            try
            {
                conn.Open();
                SqlCommand command1 = new SqlCommand(sql, conn);
                SqlDataReader reader = command1.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader["columnname"].ToString(), reader["explain"].ToString());//reader["columnname"].ToString().Substring(0, 1).ToLower() + reader["columnname"].ToString().Substring(1)
                } reader.Close();
                //this.label1.Text = "已成功的得到该数据库下的所有表！！";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return list;

        }
        public SqlDataReader GetReader(string classname)
        {
            SqlConnection conn = new SqlConnection(str);
            string dbname = string.Empty;
            //if (this.comboBox1.Text.Contains("."))
            //{
            dbname = "[" + this.comboBox1.Text + "]";
            //}
            string sql = "use " + dbname + "  ; EXEC sp_columns '" + classname + "'";
            try
            {
                conn.Open();
                SqlCommand command1 = new SqlCommand(sql, conn);
                SqlDataReader reader = command1.ExecuteReader();
                return reader;
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
            finally
            {
                //conn.Close();
            }
            return null;
        }
        /// <summary>
        /// 进行类型转换
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public string getType(string type)
        {
            switch (type)
            {
                case "varchar":
                case "varbinary":
                case "ntext":
                case "char":
                case "nvarchar":
                case "nchar":
                case "text":
                    return "string";

                case "smallint":
                case "int":
                case "bit":
                case "bigint":
                case "int identity":
                    return "int";

                case "money":
                case "float":
                case "decimal":
                case "numeric":
                case "smallmoney":
                    return "double";

                case "smalldatetime":
                case "datetime":
                case "timestamp":
                    return "DateTime";
            }
            return "string";
        }
        /// <summary>
        /// 生成实体类方法
        /// </summary>
        /// <param name="type">1-普通实体，2-DTO实体</param>
        /// <param name="className"></param>
        public void PrintModel(string className, string tableexplain, int type)
        {
            //字段名、字段类型
            Dictionary<string, Tuple<string, string>> list = Print(className);
            //字段名、字段说明
            Dictionary<string, string> list2 = PrintExplain(className);
            if (type == 2)
            {
                className += "_DTO";
            }
            try
            {
                string filename = string.Empty;
                if (type == 2)
                {
                    filename = className;
                }
                else
                {
                    filename = className.Substring(0, 1).ToUpper() + className.Substring(1).ToString();
                }
                //创建文件流
                FileStream fs = new FileStream(path + filename + ".cs", FileMode.OpenOrCreate, FileAccess.Write);
               
                //创建读写器
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);

                //执行操作
                sw.WriteLine("using System;");
                sw.WriteLine("using System.Collections.Generic;");
                sw.WriteLine("using System.Linq;");
                sw.WriteLine("using System.Text;");
                sw.WriteLine("");
                sw.WriteLine("//=========================By：蔡伟良");
                if (type == 2)
                {
                    sw.WriteLine("namespace " + namespacestr );
                }
                else
                {
                    sw.WriteLine("namespace " + namespacestr + ".Model");
                }                
                sw.WriteLine("{");
                sw.WriteLine("    /// <summary>");
                sw.WriteLine("    ///" + tableexplain);
                sw.WriteLine("    /// </summary>");
                if (type == 2)
                {
                    sw.WriteLine("    public partial class " + className);
                }
                else
                {
                    sw.WriteLine("    public class " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString());
                }                
                sw.WriteLine("    {");
                List<string> fk = new List<string>();
                using (SqlConnection conn = new SqlConnection(str))
                {
                    conn.Open();
                    string dbname = string.Empty;
                    //if (this.comboBox1.Text.Contains("."))
                    //{
                    dbname = "[" + this.comboBox1.Text + "]";
                    //}
                    string sql = "use " + dbname + "; select oMain.name from sys.foreign_keys fk JOIN sys.all_objects oSub ON (fk.parent_object_id = oSub.object_id) JOIN sys.all_objects oMain ON (fk.referenced_object_id = oMain.object_id) JOIN sys.foreign_key_columns fkCols ON (fk.object_id = fkCols.constraint_object_id) JOIN sys.columns SubCol ON (oSub.object_id = SubCol.object_id AND fkCols.parent_column_id = SubCol.column_id) where oSub.name = '表名' and SubCol.name =  '字段名'";
                    string pkname = "";
                    foreach (string dd in list.Keys)
                    {
                        pkname = "";
                        sql = "use " + dbname + " ; select oMain.name from sys.foreign_keys fk JOIN sys.all_objects oSub ON (fk.parent_object_id = oSub.object_id) JOIN sys.all_objects oMain ON (fk.referenced_object_id = oMain.object_id) JOIN sys.foreign_key_columns fkCols ON (fk.object_id = fkCols.constraint_object_id) JOIN sys.columns SubCol ON (oSub.object_id = SubCol.object_id AND fkCols.parent_column_id = SubCol.column_id) where oSub.name = '" + className + "' and SubCol.name =  '" + dd + "'";
                        try
                        {
                            SqlCommand command1 = new SqlCommand(sql, conn);
                            SqlDataReader reader = command1.ExecuteReader();
                            while (reader.Read())
                            {
                                pkname = reader["name"].ToString();
                            }
                            reader.Close();
                        }
                        catch (Exception ex)
                        {
                            this.label1.Text = ex.Message;
                            return;
                        }
                        if (String.IsNullOrEmpty(pkname))
                        {
                            sw.WriteLine("        /// <summary>");
                            if (list2.Keys.Contains(dd))
                            {
                                sw.WriteLine("        ///" + list2[dd]);
                            }                            
                            sw.WriteLine("        /// </summary>");
                            //sw.WriteLine("        private " + list[dd].Item1 + " _" + dd + ";");
                            //sw.WriteLine("");
                            //if (type == 2)
                            //{
                            //    sw.WriteLine("        public " + list[dd].Item1 + " " + dd);
                            //}
                            //else
                            //{
                            //    sw.WriteLine("        public " + list[dd].Item1 + " " + dd.Substring(0, 1).ToUpper() + dd.Substring(1).ToString());
                            //}                           
                            //sw.WriteLine("        {");
                            //sw.WriteLine("            get { return _" + dd + "; }");
                            //sw.WriteLine("            set { _" + dd + " = value; }");
                            //sw.WriteLine("        }");
                            string ctype = list[dd].Item1;
                            if (valuetype.Contains(ctype))
                            {
                                if (list[dd].Item2 == "YES")
                                {
                                    ctype = "Nullable<" + ctype + ">";
                                }
                            }
                            if (type == 2)
                            {
                                sw.WriteLine("        public " + ctype + " " + dd + " { get; set; }");
                            }
                            else
                            {
                                sw.WriteLine("        public " + ctype + " " + dd.Substring(0, 1).ToUpper() + dd.Substring(1).ToString() + " { get; set; }");
                            }
                        }
                        else
                        {
                            fk.Add(pkname + "+" + dd);
                            sw.WriteLine("        /// <summary>");
                            if (list2.Keys.Contains(dd))
                            {
                                sw.WriteLine("        ///" + list2[dd]);
                            }                            
                            sw.WriteLine("        /// </summary>");
                            sw.WriteLine("        private " + pkname.Substring(0, 1).ToUpper() + pkname.Substring(1).ToString() + " " + pkname + "_FK_" + dd + ";");
                            sw.WriteLine("");
                            sw.WriteLine("        public " + pkname.Substring(0, 1).ToUpper() + pkname.Substring(1).ToString() + " " + pkname.Substring(0, 1).ToUpper() + pkname.Substring(1).ToString() + "_FK_" + dd);
                            sw.WriteLine("        {");
                            sw.WriteLine("            get { return " + pkname + "_FK_" + dd + "; }");
                            sw.WriteLine("            set { " + pkname + "_FK_" + dd + " = value; }");
                            sw.WriteLine("        }");
                        }

                    }
                }
                if (fk.Count > 0)
                {
                    if (type == 2)
                    {
                        sw.WriteLine("        public " + className + "()");
                    }
                    else
                    {
                        sw.WriteLine("        public " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "()");
                    }
                    
                    sw.WriteLine("        {");
                    foreach (string item in fk)
                    {
                        string[] arr = item.Split('+');
                        sw.WriteLine("            this." + arr[0].Substring(0, 1).ToUpper() + arr[0].Substring(1).ToString() + "_FK_" + arr[1] + " = new " + arr[0].Substring(0, 1).ToUpper() + arr[0].Substring(1).ToString() + "();");
                    }
                    sw.WriteLine("        }");
                }
                sw.WriteLine("    }");
                sw.WriteLine("}");
                //关闭写入器
                sw.Close();

                //关闭文件流
                fs.Close();

                this.label1.Text = "生成成功！~!~";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        /// <summary>
        /// 生成实体数据访问DAL
        /// </summary>
        /// <param name="className"></param>
        public void PrintModelService(string className, string tableexplain)
        {
            Dictionary<string, string> list = GetMethod(className);
            Dictionary<string, string> field = Getfield(className);
            string id = "Id";
            try
            {
                //创建文件流
                FileStream fs = new FileStream(path + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Service.cs", FileMode.OpenOrCreate, FileAccess.Write);

                //创建读写器
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);

                //执行操作
                sw.WriteLine("using System;");
                sw.WriteLine("using System.Collections.Generic;");
                sw.WriteLine("using System.Text;");
                sw.WriteLine("using System.Data;");
                sw.WriteLine("using System.Data.SqlClient;");
                sw.WriteLine("using jsm.SqlHelper;");
                sw.WriteLine("using " + namespacestr + ".Model;");
                sw.WriteLine("");
                sw.WriteLine("//=========================By：蔡伟良");
                sw.WriteLine("namespace " + namespacestr + ".DAL");
                sw.WriteLine("{");
                sw.WriteLine("    /// <summary>");
                sw.WriteLine("    ///" + tableexplain + "Service");
                sw.WriteLine("    /// </summary>");
                sw.WriteLine("    public class " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Service");
                sw.WriteLine("    {"); 
                #region 获取所有
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 获取所有");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public  IList<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "> GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "()");
                sw.WriteLine("        {");
                sw.WriteLine("             string sql=\"select * from " + className + "\";");
                sw.WriteLine("             SqlParameter[] para = new SqlParameter[] ");
                sw.WriteLine("             {");
                sw.WriteLine("             };");
                sw.WriteLine("             List<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "> list = new List<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + ">();");
                sw.WriteLine("             using (SqlDataReader dr = SqlServerHelper.ExecuteReader(SqlServerHelper.default_connection_str, CommandType.Text, sql, para))");
                sw.WriteLine("             {");
                sw.WriteLine("                 while (dr.Read())");
                sw.WriteLine("                 {");
                sw.WriteLine("                    " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + " = new " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "();");
                SqlDataReader reader = GetReader(className);

                while (reader.Read())
                {
                    string column = reader["COLUMN_NAME"].ToString();
                    sw.WriteLine("                    " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + column.Substring(0, 1).ToUpper() + column.Substring(1) + " = (" + getType(reader["TYPE_NAME"].ToString()) + ")reader[\"" + reader["COLUMN_NAME"] + "\"];");
                }
                reader = null;
                sw.WriteLine("                    list.Add(" + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ");     ");
                sw.WriteLine("                 }");
                sw.WriteLine("             }");
                sw.WriteLine("             return list;");
                sw.WriteLine("        }");
                #endregion
                #region 获取所有总数
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 获取所有总数");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Count()");
                sw.WriteLine("        {");
                sw.WriteLine("            string sql=\"select count(*) from " + className + "\";");
                sw.WriteLine("            SqlParameter[] para = new SqlParameter[] ");
                sw.WriteLine("            {");
                sw.WriteLine("            };");
                sw.WriteLine("            int count = (int)SqlServerHelper.ExecuteScalar(SqlServerHelper.default_connection_str, CommandType.Text, sql, para);");
                sw.WriteLine("            return count;");
                sw.WriteLine("        }");
                #endregion
                #region 分页获取所有
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 分页获取所有");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public  IList<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "> GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(int pageSize, int pageIndex)");
                sw.WriteLine("        {");
                sw.WriteLine("             string sql=\"select * from (select ROW_NUMBER() OVER(order by id) as idRank,* from " + className + ") as Rank where idRank between @top and @end\";");
                sw.WriteLine("             SqlParameter[] para = new SqlParameter[] ");
                sw.WriteLine("             {");
                sw.WriteLine("                    new SqlParameter(\"@top\",pageSize*(pageIndex-1)+1),");
                sw.WriteLine("                    new SqlParameter(\"@end\",pageSize*pageIndex)");
                sw.WriteLine("             };");
                sw.WriteLine("             List<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "> list = new List<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + ">();");
                sw.WriteLine("             using (SqlDataReader dr = SqlServerHelper.ExecuteReader(SqlServerHelper.default_connection_str, CommandType.Text, sql, para))");
                sw.WriteLine("             {");
                sw.WriteLine("                 while (dr.Read())");
                sw.WriteLine("                 {");
                sw.WriteLine("                    " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + " = new " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "();");

                reader = GetReader(className);
                while (reader.Read())
                {
                    sw.WriteLine("                    " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + " = (" + getType(reader["TYPE_NAME"].ToString()) + ")reader[\"" + reader["COLUMN_NAME"] + "\"];");

                }
                reader = null;
                sw.WriteLine("                    list.Add(" + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ");     ");
                sw.WriteLine("                 }");
                sw.WriteLine("             }");
                sw.WriteLine("             return list;");
                sw.WriteLine("        }");
                #endregion
                #region 根据id获取
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 根据id获取");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public static " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " Get" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "ById(int " + className + id + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            string sql=\"select * from " + className + " where " + id + "=\"+" + className + id + ";");
                sw.WriteLine("            " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + " = new " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "();");
                sw.WriteLine("             using (SqlDataReader dr = SqlServerHelper.ExecuteReader(SqlServerHelper.default_connection_str, CommandType.Text, sql, para))");
                sw.WriteLine("             {");
                sw.WriteLine("                 while (dr.Read())");
                sw.WriteLine("                 {");
                reader = GetReader(className);
                while (reader.Read())
                {
                    sw.WriteLine("                    " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + " = (" + getType(reader["TYPE_NAME"].ToString()) + ")reader[\"" + reader["COLUMN_NAME"] + "\"];");

                }
                reader = null;
                sw.WriteLine("                 }");
                sw.WriteLine("             }");
                sw.WriteLine("             return " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ";");
                sw.WriteLine("        }");
                #endregion
                #region 修改
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 修改H_box");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int Modify" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            string sql=\"update " + className + " set \"+");
                reader = GetReader(className);
                int i = 0;
                while (reader.Read())
                {
                    if (reader["COLUMN_NAME"].ToString() != id)
                    {
                        i++;
                        if (i == 1)
                        {
                            sw.WriteLine("            \"" + reader["COLUMN_NAME"] + "=@" + reader["COLUMN_NAME"] + "\"+");
                        }
                        else
                        {
                            sw.WriteLine("            \"," + reader["COLUMN_NAME"] + "=@" + reader["COLUMN_NAME"] + "\"+");
                        }
                    }
                }
                sw.WriteLine("            \" WHERE " + id + " = @" + id + "\";");
                sw.WriteLine("            SqlParameter[] para = new SqlParameter[]");
                sw.WriteLine("            {");
                reader = GetReader(className);
                int j = 0;
                while (reader.Read())
                {
                    if (reader["COLUMN_NAME"].ToString() != id)
                    {
                        j++;
                        if (field != null && field.Count > 0)
                        {
                            foreach (string ide in field.Keys)
                            {
                                if (reader["COLUMN_NAME"].Equals(ide))
                                {
                                    if (j == 1)
                                    {
                                        sw.WriteLine("               new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + field[ide].ToString() + ")");
                                    }
                                    else
                                    {
                                        sw.WriteLine("               ,new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + field[ide].ToString() + ")");
                                    }
                                    break;
                                }
                                else
                                {
                                    if (j == 1)
                                    {
                                        sw.WriteLine("               new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                                    }
                                    else
                                    {
                                        sw.WriteLine("               ,new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                                    }
                                    break;
                                }
                            }
                        }
                        else
                        {
                            if (j == 1)
                            {
                                sw.WriteLine("               new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                            }
                            else
                            {
                                sw.WriteLine("               ,new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                            }
                        }
                    }
                }
                sw.WriteLine("            };");
                sw.WriteLine("            int flag = SqlServerHelper.ExecuteNonQuery(SqlServerHelper.default_connection_str, CommandType.Text, sql, para);");
                sw.WriteLine("            return flag;");
                sw.WriteLine("        }");
                #endregion
                #region 删除
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 删除");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int Delete" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "ById(int " + className + id + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            string sql = \"DELETE " + className + " WHERE " + id + " = @" + className + id + "\";");
                sw.WriteLine("            int flag = SqlServerHelper.ExecuteNonQuery(SqlServerHelper.default_connection_str, CommandType.Text, sql, para);");
                sw.WriteLine("            return flag;");
                sw.WriteLine("        }");
                #endregion
                #region 添加
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 添加");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int Add" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            string sql = \"insert into " + className + " values(\"+");
                reader = GetReader(className);
                int k = 0;
                while (reader.Read())
                {
                    if (reader["COLUMN_NAME"].ToString() != id)
                    {
                        k++;
                        if (k == 1)
                        {
                            sw.WriteLine("               \"@" + reader["COLUMN_NAME"] + "\"+");
                        }
                        else
                        {
                            sw.WriteLine("               \",@" + reader["COLUMN_NAME"] + "\"+");
                        }
                    }

                }
                sw.WriteLine("                 \");\";");
                sw.WriteLine("            SqlParameter[] parameter = new SqlParameter[]");
                sw.WriteLine("            {");
                reader = GetReader(className);
                int m = 0;
                while (reader.Read())
                {

                    if (reader["COLUMN_NAME"].ToString() != id)
                    {
                        m++;
                        if (field != null && field.Count > 0)
                        {
                            foreach (string ide in field.Keys)
                            {
                                if (reader["COLUMN_NAME"].Equals(ide))
                                {
                                    if (m == 1)
                                    {
                                        sw.WriteLine("                new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + field[ide].ToString() + ")");
                                    }
                                    else
                                    {
                                        sw.WriteLine("               ,new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + field[ide].ToString() + ")");
                                    }
                                    break;
                                }
                                else
                                {
                                    if (m == 1)
                                    {
                                        sw.WriteLine("                new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                                    }
                                    else
                                    {
                                        sw.WriteLine("               ,new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                                    }
                                    break;
                                }
                            }
                        }
                        else
                        {
                            if (m == 1)
                            {
                                sw.WriteLine("                new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                            }
                            else
                            {
                                sw.WriteLine("               ,new SqlParameter(\"@" + reader["COLUMN_NAME"] + "\", " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + "." + reader["COLUMN_NAME"].ToString().Substring(0, 1).ToUpper() + reader["COLUMN_NAME"].ToString().Substring(1) + ")");
                            }
                        }

                    }
                }
                sw.WriteLine("            };");
                sw.WriteLine("            int flag = SqlServerHelper.ExecuteNonQuery(SqlServerHelper.default_connection_str, CommandType.Text, sql, para);");
                sw.WriteLine("            return flag;");
                sw.WriteLine("        }");
                #endregion
                sw.WriteLine("    }");
                sw.WriteLine("}");
                //关闭写入器
                sw.Close();
                //关闭文件流
                fs.Close();
                this.label1.Text = "生成成功！";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }

        }
        /// <summary>
        /// 生成实体业务逻辑BLL
        /// </summary>
        /// <param name="className"></param>
        public void PrintModelBusiness(string className, string tableexplain)
        {
            Dictionary<string, string> list = GetMethod(className);
            string id = "Id";
            try
            {
                //创建文件流
                FileStream fs = new FileStream(path + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Business.cs", FileMode.OpenOrCreate, FileAccess.Write);

                //创建读写器
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);

                //执行操作
                sw.WriteLine("using System;");
                sw.WriteLine("using System.Collections.Generic;");
                sw.WriteLine("using System.Text;");
                sw.WriteLine("using System.Data;");
                sw.WriteLine("using System.Data.SqlClient;");
                sw.WriteLine("using " + namespacestr + ".Model;");
                sw.WriteLine("using " + namespacestr + ".DAL;");
                sw.WriteLine("");
                sw.WriteLine("//=========================By：蔡伟良");
                sw.WriteLine("namespace " + namespacestr + ".BLL");
                sw.WriteLine("{");
                sw.WriteLine("    /// <summary>");
                sw.WriteLine("    ///" + tableexplain + "Business");
                sw.WriteLine("    /// </summary>");
                sw.WriteLine("    public class " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Business");
                sw.WriteLine("    {");
                sw.WriteLine("        " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Service service = new " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Service();");
                #region 获取所有
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 获取所有");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public IList<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "> GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "()");
                sw.WriteLine("        {");
                sw.WriteLine("            return service.GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "();");
                sw.WriteLine("        }");
                #endregion
                #region 获取所有总数
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 获取所有总数");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Count()");
                sw.WriteLine("        {");
                sw.WriteLine("            return service.GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "Count();");
                sw.WriteLine("        }");
                #endregion
                #region 分页获取所有
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 分页获取所有");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public  IList<" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "> GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(int pageSize, int pageIndex)");
                sw.WriteLine("        {");
                sw.WriteLine("            return service.GetAll" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(pageSize, pageIndex);");
                sw.WriteLine("        }");
                #endregion
                #region 根据id获取
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 根据id获取");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public static " + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " Get" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "ById(int " + className + id + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            return service.Get" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "ById(" + className + id + ");");
                sw.WriteLine("        }");
                #endregion
                #region 修改
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 修改H_box");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int Modify" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            return service.Modify" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(" + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ");");
                sw.WriteLine("        }");
                #endregion
                #region 删除
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 删除");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int Delete" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "ById(int " + className + id + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            return service.Delete" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "ById(" + className + id + ");");
                sw.WriteLine("        }");
                #endregion
                #region 添加
                sw.WriteLine("        /// <summary>");
                sw.WriteLine("        /// 添加");
                sw.WriteLine("        /// </summary>");
                sw.WriteLine("        public int Add" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + " " + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ")");
                sw.WriteLine("        {");
                sw.WriteLine("            return service.Add" + className.Substring(0, 1).ToUpper() + className.Substring(1).ToString() + "(" + className.Substring(0, 1).ToLower() + className.Substring(1).ToString() + ");");
                sw.WriteLine("        }");
                #endregion
                sw.WriteLine("    }");
                sw.WriteLine("}");
                //关闭写入器
                sw.Close();
                //关闭文件流
                fs.Close();
                this.label1.Text = "生成成功！";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        //解压sqlHelper项目
        private void btnCreatDAL_Click(object sender, EventArgs e)
        {
            btntype = "sql";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            try
            {
                ZipClass.UnZip("jsm.sqlhelper.zip", path);
                this.label1.Text = "SqlHelper项目创建成功！";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        /// <summary>
        /// 生成entityBiz
        /// </summary>
        /// <param name="list"></param>
        /// <param name="className"></param>
        public void PrintEntityBiz(string className)
        {
            List<string> list = getColumnName(className);
            try
            {
                //创建文件流
                FileStream fs = new FileStream(path + className + "Biz.cs", FileMode.OpenOrCreate, FileAccess.Write);

                //创建读写器
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);

                //执行操作
                sw.WriteLine("using System;");
                sw.WriteLine("using System.Collections.Generic;");
                sw.WriteLine("using System.Linq;");
                sw.WriteLine("using System.Text;");
                sw.WriteLine("using " + e_namespacestr + ";");
                sw.WriteLine("");
                sw.WriteLine("//=========================By：蔡伟良");
                sw.WriteLine("");
                sw.WriteLine("namespace " + namespacestr + ".Biz");
                sw.WriteLine("{");
                sw.WriteLine("    public class " + className + "Biz");
                sw.WriteLine("    {");
                #region 添加
                sw.WriteLine("        //添加" + className);
                sw.WriteLine("        public int addEntity(" + className + " newentity)");
                sw.WriteLine("        {");
                sw.WriteLine("             int result = 0;");
                sw.WriteLine("             using (var Entities = new " + entitysname + "())");
                sw.WriteLine("             {");
                sw.WriteLine("                 " + className + " entity = new " + className + "()");
                sw.WriteLine("                 {");
                for (int i = 0; i < list.Count; i++)
                {
                    if (i == 0)
                    {

                    }
                    else if (i == list.Count - 1)
                    {
                        sw.WriteLine("                     " + list[i] + " = newentity." + list[i]);
                    }
                    else
                    {
                        sw.WriteLine("                     " + list[i] + " = newentity." + list[i] + ",");
                    }
                }
                sw.WriteLine("                 };");
                sw.WriteLine("                 Entities.AddTo" + className + "(entity);");
                sw.WriteLine("                 result = Entities.SaveChanges();");
                sw.WriteLine("             }");
                sw.WriteLine("             return result;");
                sw.WriteLine("        }");
                #endregion
                #region 删除
                sw.WriteLine("        //删除" + className + ",不使用");
                sw.WriteLine("        [Obsolete]");
                sw.WriteLine("        public int removeEntity(int id)");
                sw.WriteLine("        {");
                sw.WriteLine("             int result = 0;");
                sw.WriteLine("             using (var Entities = new " + entitysname + "())");
                sw.WriteLine("             {");
                sw.WriteLine("                 var entity = Entities." + className + ".Where(p => p.id == id).FirstOrDefault();");
                sw.WriteLine("                 if (entity != null)");
                sw.WriteLine("                 {");
                sw.WriteLine("                     Entities.DeleteObject(entity);");
                sw.WriteLine("                     result = Entities.SaveChanges();");
                sw.WriteLine("                 }");
                sw.WriteLine("             }");
                sw.WriteLine("             return result;");
                sw.WriteLine("        }");
                #endregion
                #region 修改
                sw.WriteLine("        //修改" + className + "");
                sw.WriteLine("        public int updateEntity(" + className + " newentity)");
                sw.WriteLine("        {");
                sw.WriteLine("             int result = 0;");
                sw.WriteLine("             using (var Entities = new " + entitysname + "())");
                sw.WriteLine("             {");
                sw.WriteLine("                 var entity = Entities." + className + ".Where(p => p.id == newentity.id).FirstOrDefault();");
                sw.WriteLine("                 if (entity != null)");
                sw.WriteLine("                 {");
                for (int i = 0; i < list.Count; i++)
                {
                    if (i == 0)
                    {

                    }
                    else
                    {
                        sw.WriteLine("                     entity." + list[i] + " = newentity." + list[i] + ";");
                    }
                }
                sw.WriteLine("                     result = Entities.SaveChanges();");
                sw.WriteLine("                 }");
                sw.WriteLine("             }");
                sw.WriteLine("             return result;");
                sw.WriteLine("        }");
                #endregion
                #region findById
                sw.WriteLine("        //根据id获取" + className);
                sw.WriteLine("        public " + className + " findById(int id)");
                sw.WriteLine("        {");
                sw.WriteLine("             int result = 0;");
                sw.WriteLine("             using (var Entities = new " + entitysname + "())");
                sw.WriteLine("             {");
                sw.WriteLine("                 var entity = Entities." + className + ".Where(p => p.id == id).FirstOrDefault();");
                sw.WriteLine("                 if (entity == null)");
                sw.WriteLine("                 {");
                sw.WriteLine("                     return null;");
                sw.WriteLine("                 }");
                sw.WriteLine("                 else");
                sw.WriteLine("                 {");
                sw.WriteLine("                     return entity;");
                sw.WriteLine("                 }");
                sw.WriteLine("             }");
                sw.WriteLine("        }");
                #endregion
                #region findAll
                sw.WriteLine("        //获取所有" + className);
                sw.WriteLine("        public List<" + className + "> findAll()");
                sw.WriteLine("        {");
                sw.WriteLine("             using (var Entities = new " + entitysname + "())");
                sw.WriteLine("             {");
                sw.WriteLine("                 var entitylist = Entities." + className + ".OrderByDescending(p => p.id);");
                sw.WriteLine("                 return entitylist.ToList<" + className + ">();");
                sw.WriteLine("             }");
                sw.WriteLine("        }");
                #endregion
                #region findAllCount
                sw.WriteLine("        //获取" + className + "总数");
                sw.WriteLine("        public int findAllCount()");
                sw.WriteLine("        {");
                sw.WriteLine("             using (var Entities = new " + entitysname + "())");
                sw.WriteLine("             {");
                sw.WriteLine("                 return Entities." + className + ".Count();");
                sw.WriteLine("             }");
                sw.WriteLine("        }");
                #endregion
                #region findAllByPage
                sw.WriteLine("        //获取所有" + className);
                sw.WriteLine("        public List<" + className + "> findAll(int pageIndex, int pageSize)");
                sw.WriteLine("        {");
                sw.WriteLine("             using (var Entities = new " + entitysname + "())");
                sw.WriteLine("             {");
                sw.WriteLine("                 var entitylist = Entities." + className + ".OrderByDescending(p => p.id).Skip(pageSize * (pageIndex - 1)).Take(pageSize);");
                sw.WriteLine("                 return entitylist.ToList<" + className + ">();");
                sw.WriteLine("             }");
                sw.WriteLine("        }");
                #endregion
                //
                sw.WriteLine("    }");
                sw.WriteLine("}");
                //关闭写入器
                sw.Close();

                //关闭文件流
                fs.Close();

                this.label1.Text = "生成成功！~!~";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        
        /// <summary>
        /// 修改app.config
        /// </summary>
        /// <param name="AppKey">节点名</param>
        /// <param name="AppValue">值</param>
        public static void SaveAppConfig(string AppKey, string AppValue)
        {
            System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();
            xDoc.Load(System.Windows.Forms.Application.ExecutablePath + ".config");

            System.Xml.XmlNode xNode;
            System.Xml.XmlElement xElem1;
            System.Xml.XmlElement xElem2;
            xNode = xDoc.SelectSingleNode("//appSettings");

            xElem1 = (System.Xml.XmlElement)xNode.SelectSingleNode("//add[@key='" + AppKey + "']");
            if (xElem1 != null) xElem1.SetAttribute("value", AppValue);
            else
            {
                xElem2 = xDoc.CreateElement("add");
                xElem2.SetAttribute("key", AppKey);
                xElem2.SetAttribute("value", AppValue);
                xNode.AppendChild(xElem2);
            }
            xDoc.Save(System.Windows.Forms.Application.ExecutablePath + ".config");
        }


        /// <summary>
        /// 获取表字段名
        /// </summary>
        /// <param name="className"></param>
        /// <returns></returns>
        public List<string> getColumnName(string className)
        {
            List<string> list = new List<string>();
            SqlConnection conn = new SqlConnection(str);
            string dbname = string.Empty;
            //if (this.comboBox1.Text.Contains("."))
            //{
            dbname = "[" + this.comboBox1.Text + "]";
            //}
            string sql = "use " + dbname + "  ; select name from syscolumns where ID=OBJECT_ID('" + className + "')";
            try
            {
                conn.Open();
                SqlCommand command1 = new SqlCommand(sql, conn);
                SqlDataReader reader = command1.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader["name"].ToString());
                }
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }

        private void button5_Click(object sender, EventArgs e)
        {
            this.folderBrowserDialog1.ShowDialog();
            this.txtPath.Text = this.folderBrowserDialog1.SelectedPath;
            path = this.folderBrowserDialog1.SelectedPath + @"\";
        }

        //生成数据实体
        private void btnCreatModel_Click(object sender, EventArgs e)
        {
            if (this.txtNameSpace.Text.Trim() == "")
            {
                MessageBox.Show("请输入命名空间");
                return;
            }
            btntype = "model";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            namespacestr = this.txtNameSpace.Text.Trim();
            try
            {
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    if (this.checkedListBox1.GetItemChecked(i))
                    {
                        string[] arr = this.checkedListBox1.Items[i].ToString().Split('/');
                        PrintModel(arr[0], arr[1], 1);
                    }
                }

            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        //生成数据实体业务逻辑
        private void btnCreatBusiness_Click(object sender, EventArgs e)
        {
            if (this.txtNameSpace.Text.Trim() == "")
            {
                MessageBox.Show("请输入命名空间");
                return;
            }
            btntype = "business";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            namespacestr = this.txtNameSpace.Text.Trim();
            try
            {
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    if (this.checkedListBox1.GetItemChecked(i))
                    {
                        string[] arr = this.checkedListBox1.Items[i].ToString().Split('/');
                        PrintModelBusiness(arr[0], arr[1]);
                    }
                }

            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        //生成数据实体数据访问
        private void btnCreatService_Click(object sender, EventArgs e)
        {
            if (this.txtNameSpace.Text.Trim() == "")
            {
                MessageBox.Show("请输入命名空间");
                return;
            }
            btntype = "service";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            namespacestr = this.txtNameSpace.Text.Trim();
            try
            {
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    if (this.checkedListBox1.GetItemChecked(i))
                    {
                        string[] arr = this.checkedListBox1.Items[i].ToString().Split('/');
                        PrintModelService(arr[0], arr[1]);
                    }
                }

            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        //生成linqBiz
        private void btnCreatBiz_Click(object sender, EventArgs e)
        {
            if (this.txtNameSpace_linq.Text.Trim() == "")
            {
                MessageBox.Show("请输入命名空间");
                return;
            }
            if (this.txtEntitysSpaceName.Text.Trim() == "")
            {
                MessageBox.Show("请输入Entities命名空间");
                return;
            }
            if (this.txtEntitysName.Text.Trim() == "")
            {
                MessageBox.Show("请输入实体集名称");
                return;
            }
            btntype = "linq";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            namespacestr = this.txtNameSpace_linq.Text.Trim();
            e_namespacestr = this.txtEntitysSpaceName.Text.Trim();
            entitysname = this.txtEntitysName.Text.Trim();
            try
            {
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    if (this.checkedListBox1.GetItemChecked(i))
                    {
                        PrintEntityBiz(this.checkedListBox1.Items[i].ToString());
                    }
                }

            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            e.Result = ComputeFibonacci(backgroundWorker1, e);//当ComputeFibonacci(worker, e)返回时，异步过程结束   
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            if (btntype == "linq")
            {
                this.pbLinq.Value = e.ProgressPercentage;
            }
            else
            {
                this.pb3.Value = e.ProgressPercentage;
            }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //MessageBox.Show("完成！");
            this.btnCreatModel.Enabled = true;
            this.btnCreatService.Enabled = true;
            this.btnCreatBusiness.Enabled = true;
            this.btnCreatDAL.Enabled = true;
            this.btnCreatBiz.Enabled = true;
            this.pbLinq.Value = 0;
            this.pb3.Value = 0;
        }
        /// <summary>
        /// 计算百分比
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        /// <returns></returns>
        private int ComputeFibonacci(object sender, DoWorkEventArgs e)
        {
            for (int i = 0; i < 1000; i++)
            {
                if (backgroundWorker1.CancellationPending)
                {
                    e.Cancel = true;
                    return -1;
                }
                else
                {
                    int percent = 100 * i / 1000;//计算已完成的百分比  
                    backgroundWorker1.ReportProgress(percent);
                }
                //System.Threading.Thread.Sleep(10);
            }
            return -1;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                bool flag = false;
                if (this.checkBox1.Checked)
                {
                    flag = true;
                }
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    this.checkedListBox1.SetItemChecked(i, flag);
                }
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (this.txtNameSpace.Text.Trim() == "")
            {
                MessageBox.Show("请输入命名空间");
                return;
            }
            btntype = "model";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            namespacestr = this.txtNameSpace.Text.Trim();
            SaveAppConfig("namespaces", this.txtNameSpace.Text.Trim());
            try
            {
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    if (this.checkedListBox1.GetItemChecked(i))
                    {
                        string[] arr = this.checkedListBox1.Items[i].ToString().Split('/');
                        PrintModel(arr[0], arr[1], 2);
                    }
                }
                SaveAppConfig("path", this.txtPath.Text.Trim());
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            btntype = "model";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            try
            {
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    if (this.checkedListBox1.GetItemChecked(i))
                    {
                        string[] arr = this.checkedListBox1.Items[i].ToString().Split('/');
                        PrintTypeMap(arr[0]);
                    }
                }

            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }

        /// <summary>
        /// 生成实体类zx
        /// </summary>
        /// <param name="tableName"></param>
        public void PrintModel_ZX(string tableName, string tableexplain)
        {
            //字段名、字段类型
            Dictionary<string, Tuple<string,string>> list = Print(tableName);
            //字段名、字段说明
            Dictionary<string, string> list2 = PrintExplain(tableName);
            //类名
            string className = "";
            string[] temp = tableName.Split('_');
            for (int i = 1; i < temp.Length; i++)
            {
                string item = temp[i].ToLower();
                className += item.Substring(0, 1).ToUpper() + item.Substring(1).ToString();
            }
            try
            {
                string filename = className;
                //创建文件流
                FileStream fs = new FileStream(path + filename + ".cs", FileMode.OpenOrCreate, FileAccess.Write);

                //创建读写器
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);

                //执行操作
                sw.WriteLine("using System;");
                sw.WriteLine("using System.Collections.Generic;");
                sw.WriteLine("using System.Linq;");
                sw.WriteLine("using System.Text;");
                sw.WriteLine("");
                sw.WriteLine("namespace MODEL");
                sw.WriteLine("{");
                sw.WriteLine("    /// <summary>");
                sw.WriteLine("    ///" + tableexplain);
                sw.WriteLine("    /// </summary>");
                sw.WriteLine("    public class " + className);
                sw.WriteLine("    {");
                foreach (string dd in list.Keys)
                {
                    sw.WriteLine("        /// <summary>");
                    if (list2.Keys.Contains(dd))
                    {
                        sw.WriteLine("        ///" + list2[dd]);
                    }
                    sw.WriteLine("        /// </summary>");
                    string ctype = list[dd].Item1;
                    if (valuetype.Contains(ctype))
                    {
                        if (list[dd].Item2 == "YES")
                        {
                            ctype = "Nullable<"+ ctype + ">";
                        }
                    }
                    if (chk_same.Checked)
                    {
                        sw.WriteLine("        public " + ctype + " " + dd + " { get; set; }");
                    }
                    else
                    {
                        if (dd.Contains("_"))
                        {
                            string columnname = "";
                            string[] temp2 = dd.Split('_');
                            for (int i = 0; i < temp2.Length; i++)
                            {
                                string item = temp2[i].ToLower();
                                columnname += item.Substring(0, 1).ToUpper() + item.Substring(1).ToString();
                            }
                            sw.WriteLine("        public " + ctype + " " + columnname + " { get; set; }");
                        }
                        else
                        {
                            sw.WriteLine("        public " + ctype + " " + dd.Substring(0, 1).ToUpper() + dd.Substring(1).ToLower().ToString() + " { get; set; }");
                        }
                    }                    
                }
                sw.WriteLine("    }");
                sw.WriteLine("}");
                //关闭写入器
                sw.Close();

                //关闭文件流
                fs.Close();

                this.label1.Text = "生成成功！~!~";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
        /// <summary>
        /// 生成实体映射zx
        /// </summary>
        /// <param name="type">1-普通实体，2-DTO实体</param>
        /// <param name="className">表名</param>
        public void PrintTypeMap(string tableName)
        {
            //字段名、字段类型
            Dictionary<string, Tuple<string,string>> list = Print(tableName);
            //字段名、字段说明
            Dictionary<string, string> list2 = PrintExplain(tableName);
            //类名
            string className = "";
            string[] temp = tableName.Split('_');
            for (int i = 1; i < temp.Length; i++)
            {
                string item = temp[i].ToLower();
                className += item.Substring(0, 1).ToUpper() + item.Substring(1).ToString();
            }     
            try
            {
                string filename = string.Empty;
                filename = className + "TypeMap";
                //创建文件流
                FileStream fs = new FileStream(path + filename + ".cs", FileMode.OpenOrCreate, FileAccess.Write);

                //创建读写器
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);

                //执行操作
                sw.WriteLine("namespace DAL");
                sw.WriteLine("{");
                sw.WriteLine("    using MODEL;"); 
                sw.WriteLine("    using System.Data.Entity.ModelConfiguration;");
                sw.WriteLine("    internal partial class " + className + "TypeMap : EntityTypeConfiguration<" + className + ">");
                sw.WriteLine("    {");
                sw.WriteLine("        public " + className + "TypeMap()");
                sw.WriteLine("        {");
                sw.WriteLine("            this.HasKey(t => t.Id);");
                sw.WriteLine("            this.ToTable(\"" + tableName + "\");");
                foreach (string dd in list.Keys)
                {
                    string HasPrecision = "";
                    if (list[dd].Item1 == "decimal")
                    {
                        HasPrecision = ".HasPrecision(18, 2)";
                    }
                    if (chk_same.Checked)
                    {
                        sw.WriteLine("            this.Property(t => t." + dd + ").HasColumnName(\"" + dd + "\")" + HasPrecision + ";");
                    }
                    else
                    {
                        if (dd.Contains("_"))
                        {
                            string columnname = "";
                            string[] temp2 = dd.Split('_');
                            for (int i = 0; i < temp2.Length; i++)
                            {
                                string item = temp2[i].ToLower();
                                columnname += item.Substring(0, 1).ToUpper() + item.Substring(1).ToString();
                            }
                            sw.WriteLine("            this.Property(t => t." + columnname + ").HasColumnName(\"" + dd + "\")" + HasPrecision + ";");
                        }
                        else
                        {
                            sw.WriteLine("            this.Property(t => t." + dd.Substring(0, 1).ToUpper() + dd.Substring(1).ToLower().ToString() + ").HasColumnName(\"" + dd + "\")" + HasPrecision + ";");
                        }
                    }
                                        
                }
                sw.WriteLine("        }");                
                sw.WriteLine("    }");
                sw.WriteLine("}");
                //关闭写入器
                sw.Close();

                //关闭文件流
                fs.Close();

                this.label1.Text = "生成成功！~!~";
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            btntype = "model";
            backgroundWorker1.RunWorkerAsync();
            this.btnCreatModel.Enabled = false;
            this.btnCreatService.Enabled = false;
            this.btnCreatBusiness.Enabled = false;
            this.btnCreatDAL.Enabled = false;
            this.btnCreatBiz.Enabled = false;
            try
            {
                for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
                {
                    if (this.checkedListBox1.GetItemChecked(i))
                    {
                        string[] arr = this.checkedListBox1.Items[i].ToString().Split('/');
                        PrintModel_ZX(arr[0], arr[1]);
                    }
                }
            }
            catch (Exception ex)
            {
                this.label1.Text = ex.Message;
            }
        }
    }
}
